package com.ba360.portal.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.TimeZone;

import com.ba360.portal.database.DBConnectionImpl;
import com.ba360.portal.database.dbo.SearchAgent;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class AutoNotifyJob1 extends HttpServlet {
	
	public void init() throws ServletException {
	}
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws IOException, ServletException {
		Log.info("Auto Notify Job started");
		
		Calendar now = Calendar.getInstance();
		Calendar orig = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		
		if (!(now.getTime().equals(orig.getTime()))) {
			return;
		}
		
		int previousUser = 0;
		int currentUser = 0;
		Connection conn = null;
		Statement stmt = null;
		Statement stmtAds = null;
		Statement stmtUser = null;
		String title = "";
		String adDesc = "";
		String body = "";
		String adv = "";
		String advID = "";
		String token = "";
		ResultSet rs = null;
		ResultSet rsUser = null;
		
		try {
			
			DBConnectionImpl dbConn = new DBConnectionImpl();
			conn = dbConn.getConnection();
			stmt = conn.createStatement();
			stmtAds = conn.createStatement();
			stmtUser = conn.createStatement();
			
			ResultSet rsAds = null;
			SearchAgent agent = null;
			
			// Get the records where create_date+timeframe >= current date orderby email_id 
			// this query work for b360 but not for p360
			//rs = stmt.executeQuery("select * from search_agent where ADDDATE(createddate, timeframe) >= (SELECT SYSDATE() FROM dual) order by userid");
			rs = stmt.executeQuery("select * from search_agent sa where adddate(sa.createddate, INTERVAL sa.timeframe DAY) >= SYSDATE() order by userid");
			
			while(rs.next()) {
				
				
				previousUser = currentUser;
				currentUser = rs.getInt("userid");
				Log.info("Userid = " + currentUser);
				
				if (previousUser == 0) {
					previousUser = currentUser;
				}
				
				if ((previousUser != currentUser) && body.length()>0) {
					
					rsUser = stmtUser.executeQuery("select firstname,lastname from userinfo where userid=" + agent.getUserID());
					rsUser.next();
					// Send mail to the maild id with the following format.
					Properties prop = new Properties();
					String firstname = rsUser.getString("firstname");
					String lastname = rsUser.getString("lastname");
					prop.setProperty("Fullname", firstname + " " + lastname);
					prop.setProperty("ToEmailAddr", agent.getEmailAddress());
					prop.setProperty("FromEmailAddr", "response@pune360.com");
					prop.setProperty("Subject", "Auto Notification Email");
					prop.setProperty("Body", body + "<br>\n");
					
					try {
						EmailSender.sendEmail(EmailSender.TYPE_AUTONOTIFY, prop);
					} catch (Exception e) {
						
						System.out.println(e);
						Log.error(e.toString());
					} finally {
						rsUser.close();
						body = "";
					}
				}
				
				agent = new SearchAgent();
				
				agent.setUserID(String.valueOf(currentUser));
				
				// Get the the keywords
				agent.setKeywords(rs.getString("keywords").trim());
				agent.setMatchtype(rs.getInt("matchtype"));
				
				//select member_id,name,ad_description,ad_id from ads where (SELECT TIMEDIFF((select sysdate() from dual),date_posted)) <= '24:00:00'
				//rsAds = stmtAds.executeQuery("select member_id,name,ad_description,ad_id from ads where (SELECT DATEDIFF((select sysdate() from dual),date_posted)) <= 1 and member_id != " + currentUser);
				rsAds = stmtAds.executeQuery("select member_id,name,ad_description,ad_id from ads where ((TO_DAYS(sysdate()) - TO_DAYS(date_posted)) <= 1) and member_id != " + currentUser);
				
				
				//search if the keywords match any/all (depends on the match type) of the name, ad_description in the ads table, if it matches
				
				if(agent.getMatchtype() == 1) {
					// All of my keywords
					while(rsAds.next()) {
						
						title = rsAds.getString("name");
						adDesc = rsAds.getString("ad_description");
						advID = rsAds.getString("ad_id");
						if (body.indexOf(advID) == -1) {
							if (title.indexOf(agent.getKeywords()) > -1) {
								body += "There is an classifieds posting that meets your search requirement.<br>\n";
								body += "Search keyword is '" + agent.getKeywords() + "'<br>\nPlease browse the following:<br>\n";
								adv = "http://www.pune360.com/portal/postadv.do?advId=" + advID + "&action=DisplayAd";
								body += "<a href=\"" + adv + "\">" +  adv + "</a><br>\n";
							} else if (adDesc.indexOf(agent.getKeywords()) > -1) {
								body += "There is an classifieds posting that meets your search requirement.<br>\n";
								body += "Search keyword is '" + agent.getKeywords() + "'<br>\nPlease browse the following:<br>\n";
								adv = "http://www.pune360.com/portal/postadv.do?advId=" + advID + "&action=DisplayAd";
								body += "<a href=\"" + adv + "\">" +  adv + "</a><br>\n";
							}
						}
					}
				} else {
					// Any of my keywords
					while(rsAds.next()) {
						
						title = rsAds.getString("name");
						adDesc = rsAds.getString("ad_description");
						advID = rsAds.getString("ad_id");
						if (body.indexOf(advID) == -1) {
							
							boolean returnTokens = true;
							String currentDelims = " ";
							StringTokenizer parser = new StringTokenizer(
									agent.getKeywords(),
									currentDelims,
									returnTokens
							);
							for (int i = 0; parser.hasMoreTokens(); i++) {
								token = parser.nextToken(currentDelims);
								if (title.indexOf(token) > -1) {
									body += "There is an classifieds posting that meets your search requirement.<br>\n";
									body += "Search keyword is '" + agent.getKeywords() + "'<br>\nPlease browse the following:<br>\n";
									adv = "http://www.pune360.com/portal/postadv.do?advId=" + advID + "&action=DisplayAd";
									body += "<a href=\"" + adv + "\">" +  adv + "</a><br>\n";
									break;
								} else if (adDesc.indexOf(token) > -1) {
									body += "There is an classifieds posting that meets your search requirement.<br>\n";
									body += "Search keyword is '" + agent.getKeywords() + "'<br>\nPlease browse the following:<br>\n";
									adv = "http://www.pune360.com/portal/postadv.do?advId=" + advID + "&action=DisplayAd";
									body += "<a href=\"" + adv + "\">" +  adv + "</a><br>\n";
									break;
								}
							}
						}
					}
				}
				
				// Get the mail id
				agent.setEmailAddress(rs.getString("emailaddress"));
				
				rsAds.close();
			}
			
			//ads only with same user problem - if ((previousUser == currentUser) && body.length()>0) {
			if (body.length()>0) {
				
				rsUser = stmtUser.executeQuery("select firstname,lastname from userinfo where userid=" + agent.getUserID());
				rsUser.next();
				// Send mail to the maild id with the following format.
				Properties prop = new Properties();
				String firstname = rsUser.getString("firstname");
				String lastname = rsUser.getString("lastname");
				prop.setProperty("Fullname", firstname + " " + lastname);
				prop.setProperty("ToEmailAddr", agent.getEmailAddress());
				prop.setProperty("FromEmailAddr", "response@pune360.com");
				prop.setProperty("Subject", "Auto Notification Email");
				prop.setProperty("Body", body + "<br>\n");
				
				try {
					EmailSender.sendEmail(EmailSender.TYPE_AUTONOTIFY, prop);
				} catch (Exception e) {
					
					System.out.println(e);
					Log.error(e.toString());
				} finally {
					rsUser.close();
				}
			}
		} catch(Exception excp) {
			Log.error("Error in getting search agents :" + excp.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
				stmtAds.close();
				stmtUser.close();
				conn.close();
				Log.info("Auto Notify Job stopped");
			} catch (SQLException sqlExcp) {
				Log.error("Error in getting search agents :" + sqlExcp.getMessage());
			}
		}
	}
}
